﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Data;
using System.Net;
using System.Net.NetworkInformation;
using MySql.Data.MySqlClient;
using NETCommon;
using NETCommon.Data;

namespace NETCommon.Files
{
    public static class NCommon
    {
        /// <summary>
        /// Check a file if exist or not
        /// </summary>
        /// <param name="strFilename">Fullname (Include Path + Filename) of file to check</param>
        /// <returns>TRUE if file exists, FALSE if not</returns>
        public static bool CheckFileExists(string strFilename)
        {
            bool bReturn = false;
            try
            {
                if (!String.IsNullOrEmpty(strFilename))
                {
                    if (File.Exists(strFilename)) bReturn = true;
                }
            }
            catch
            {
                bReturn = false;
            }
            return bReturn;
        }

        /// <summary>
        /// Filte query to elimunate "'" in data to insert into database
        /// </summary>
        /// <param name="strData">String of data to filter</param>
        /// <returns>String which already filter</returns>
        public static string FilterCommaQuery(string strData)
        {
            string strReturn = strData;
            try
            {
                strReturn = strData.Replace("'", "''");
            }
            catch
            {
            }
            return strReturn;
        }

        #region "FOR .NET COMBOBOX"

        /// <summary>
        /// Get the correct index of a combobox when we know prevalue
        /// </summary>
        /// <param name="cbo">Name of combobox</param>
        /// <param name="SelectedValue">Value of Selected Value to get index</param>
        /// <returns>Index of value</returns>
        public static int GetComboboxIndex(ComboBox cbo, string SelectedValue)
        {
            int intReturn = 0;

            try
            {
                if (cbo.Items.Count > 0)
                {
                    for (int i = 0; i < cbo.Items.Count; i++)
                    {
                        cbo.SelectedIndex = i;
                        if (cbo.SelectedValue.ToString() == SelectedValue)
                        {
                            intReturn = i;
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return intReturn;
        }

        /// <summary>
        /// Get the correct index of a combobox when we know prevalue
        /// </summary>
        /// <param name="cbo">Name of combobox</param>
        /// <param name="SelectedValue">Value of Selected Value to get index</param>
        /// <returns>Index of value</returns>
        public static int GetComboboxIndex(ComboBox cbo, int SelectedValue)
        {
            int intReturn = 0;

            try
            {
                if (cbo.Items.Count > 0)
                {
                    for (int i = 0; i < cbo.Items.Count; i++)
                    {
                        cbo.SelectedIndex = i;
                        if (cbo.SelectedValue.ToString() == SelectedValue.ToString())
                        {
                            intReturn = i;
                            break;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return intReturn;
        }

        /// <summary>
        /// Load data ffrom database to a combobox
        /// </summary>
        /// <param name="cbo">Combobox control name</param>
        /// <param name="ValueMember_Fieldname">Name of ValueMember</param>
        /// <param name="DisplayMember_Fieldname">Name of DisplayMember</param>
        /// <param name="TableName">name of Table to get data</param>
        /// <param name="Filter">String to filter database</param>
        public static void LoadDataToCombobox(ComboBox cbo, string ValueMember_Fieldname, string DisplayMember_Fieldname, string TableName, string Filter)
        {
            NDatabase objDAL = new NDatabase();
            DataSet ds = new DataSet();
            string strSQL = "";

            try
            {
                //Build query
                strSQL = "SELECT " + ValueMember_Fieldname + ", " + DisplayMember_Fieldname + " " +
                         "FROM " + TableName + " " +
                         "WHERE " + Filter;

                //Get data from database
                ds = objDAL.fcnExecQuery(strSQL, TableName);

                //Binding data to combobox
                if (ds.Tables.Count > 0)
                {
                    cbo.DataSource = ds.Tables[0];
                    cbo.DisplayMember = DisplayMember_Fieldname;
                    cbo.ValueMember = ValueMember_Fieldname;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Load data ffrom database to a combobox
        /// </summary>
        /// <param name="cbo">Combobox control name</param>
        /// <param name="ValueMember_Fieldname">Name of ValueMember</param>
        /// <param name="DisplayMember_Fieldname">Name of DisplayMember</param>
        /// <param name="TableName">name of Table to get data</param>
        /// <param name="Filter">String to filter database</param>
        public static void LoadDataToCombobox(ComboBox cbo, string ValueMember_Fieldname, string DisplayMember_Fieldname, string TableName)
        {
            NDatabase objDAL = new NDatabase();
            DataSet ds = new DataSet();
            string strSQL = "";

            try
            {
                //Build query
                strSQL = "SELECT " + ValueMember_Fieldname + ", " + DisplayMember_Fieldname + " " +
                         "FROM " + TableName;

                //Get data from database
                ds = objDAL.fcnExecQuery(strSQL, TableName);

                //Binding data to combobox
                if (ds.Tables.Count > 0)
                {
                    cbo.DataSource = ds.Tables[0];
                    cbo.DisplayMember = DisplayMember_Fieldname;
                    cbo.ValueMember = ValueMember_Fieldname;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Load data from database to a combobox including "ALL" and ValueMember is string
        /// </summary>
        /// <param name="cbo">Combobox control name</param>
        /// <param name="ValueMember_Fieldname">Name of ValueMember</param>
        /// <param name="DisplayMember_Fieldname">Name of DisplayMember</param>
        /// <param name="TableName">name of Table to get data</param>
        /// <param name="Filter">String to filter database</param>
        public static void LoadDataToCombobox_WithAll_String(ComboBox cbo, string ValueMember_Fieldname, string DisplayMember_Fieldname, string TableName, string Filter)
        {
            NDatabase objDAL = new NDatabase();
            DataSet ds = new DataSet();
            string strSQL = "";

            try
            {
                //Build query
                strSQL = "SELECT '0' AS " + ValueMember_Fieldname + ", N'.All' AS " + DisplayMember_Fieldname + " FROM " + TableName + " " +
                         "UNION " +
                         "SELECT " + ValueMember_Fieldname + ", " + DisplayMember_Fieldname + " " +
                         "FROM " + TableName + " " +
                         "WHERE " + Filter;

                //Get data from database
                ds = objDAL.fcnExecQuery(strSQL, TableName);

                //Binding data to combobox
                if (ds.Tables.Count > 0)
                {
                    cbo.DataSource = ds.Tables[0];
                    cbo.DisplayMember = DisplayMember_Fieldname;
                    cbo.ValueMember = ValueMember_Fieldname;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Load data from database to a combobox including "ALL" and ValueMember is string
        /// </summary>
        /// <param name="cbo">Combobox control name</param>
        /// <param name="ValueMember_Fieldname">Name of ValueMember</param>
        /// <param name="DisplayMember_Fieldname">Name of DisplayMember</param>
        /// <param name="TableName">name of Table to get data</param>
        /// <param name="Filter">String to filter database</param>
        public static void LoadDataToCombobox_WithAll_String(ComboBox cbo, string ValueMember_Fieldname, string DisplayMember_Fieldname, string TableName)
        {
            NDatabase objDAL = new NDatabase();
            DataSet ds = new DataSet();
            string strSQL = "";

            try
            {
                //Build query
                strSQL = "SELECT '0' AS " + ValueMember_Fieldname + ", N'.All' AS " + DisplayMember_Fieldname + " FROM " + TableName + " " +
                         "UNION " +
                         "SELECT " + ValueMember_Fieldname + ", " + DisplayMember_Fieldname + " " +
                         "FROM " + TableName;

                //Get data from database
                ds = objDAL.fcnExecQuery(strSQL, TableName);

                //Binding data to combobox
                if (ds.Tables.Count > 0)
                {
                    cbo.DataSource = ds.Tables[0];
                    cbo.DisplayMember = DisplayMember_Fieldname;
                    cbo.ValueMember = ValueMember_Fieldname;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Load data from database to a combobox including "ALL" and ValueMember is numeric
        /// </summary>
        /// <param name="cbo">Combobox control name</param>
        /// <param name="ValueMember_Fieldname">Name of ValueMember</param>
        /// <param name="DisplayMember_Fieldname">Name of DisplayMember</param>
        /// <param name="TableName">name of Table to get data</param>
        /// <param name="Filter">String to filter database</param>
        public static void LoadDataToCombobox_WithAll_Number(ComboBox cbo, string ValueMember_Fieldname, string DisplayMember_Fieldname, string TableName, string Filter)
        {
            NDatabase objDAL = new NDatabase();
            DataSet ds = new DataSet();
            string strSQL = "";

            try
            {
                //Build query
                strSQL = "SELECT 0 AS " + ValueMember_Fieldname + ", N'.All' AS " + DisplayMember_Fieldname + " FROM " + TableName + " " +
                         "UNION " +
                         "SELECT " + ValueMember_Fieldname + ", " + DisplayMember_Fieldname + " " +
                         "FROM " + TableName + " " +
                         "WHERE " + Filter;

                //Get data from database
                ds = objDAL.fcnExecQuery(strSQL, TableName);

                //Binding data to combobox
                if (ds.Tables.Count > 0)
                {
                    cbo.DataSource = ds.Tables[0];
                    cbo.DisplayMember = DisplayMember_Fieldname;
                    cbo.ValueMember = ValueMember_Fieldname;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Load data from database to a combobox including "ALL" and ValueMember is numeric
        /// </summary>
        /// <param name="cbo">Combobox control name</param>
        /// <param name="ValueMember_Fieldname">Name of ValueMember</param>
        /// <param name="DisplayMember_Fieldname">Name of DisplayMember</param>
        /// <param name="TableName">name of Table to get data</param>
        /// <param name="Filter">String to filter database</param>
        public static void LoadDataToCombobox_WithAll_Number(ComboBox cbo, string ValueMember_Fieldname, string DisplayMember_Fieldname, string TableName)
        {
            NDatabase objDAL = new NDatabase();
            DataSet ds = new DataSet();
            string strSQL = "";

            try
            {
                //Build query
                strSQL = "SELECT 0 AS " + ValueMember_Fieldname + ", N'.All' AS " + DisplayMember_Fieldname + " FROM " + TableName + " " +
                         "UNION " +
                         "SELECT " + ValueMember_Fieldname + ", " + DisplayMember_Fieldname + " " +
                         "FROM " + TableName;

                //Get data from database
                ds = objDAL.fcnExecQuery(strSQL, TableName);

                //Binding data to combobox
                if (ds.Tables.Count > 0)
                {
                    cbo.DataSource = ds.Tables[0];
                    cbo.DisplayMember = DisplayMember_Fieldname;
                    cbo.ValueMember = ValueMember_Fieldname;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        /// <summary>
        /// Get datetime string from Gdatetime
        /// </summary>
        /// <param name="epoch">Gdatetime value</param>
        /// <returns>String of datetiem as format: yyyy/MM/dd\nHH:mm:ss</returns>
        public static string GetDateTime_String_From_GDateTime(int epoch)
        {
            try
            {
                double timestamp = epoch + 9 * 60 * 60;
                DateTime dt = new DateTime(1970, 1, 1, 0, 0, 0, 0);
                dt = dt.AddSeconds(timestamp);
                return dt.ToString("yyyy/MM/dd\nHH:mm:ss");
            }
            catch
            {
                return DateTime.Now.ToString("yyyy/MM/dd\nHH:mm:ss");
            }
        }

        /// <summary>
        /// Get Datetime from Gdatetime
        /// </summary>
        /// <param name="epoch">Gdatetime value</param>
        /// <returns>DateTime value</returns>
        public static DateTime GetDateTime_From_GDateTime(int epoch)
        {
            try
            {
                double timestamp = epoch + 9 * 60 * 60;
                DateTime dt = new DateTime(1970, 1, 1, 0, 0, 0, 0);
                dt = dt.AddSeconds(timestamp);
                return dt;
            }
            catch
            {
                return DateTime.Now;
            }
        }

        /// <summary>
        /// Ping to a host
        /// </summary>
        /// <param name="IPAddress">IP or Hostname</param>
        /// <param name="timeout">timeout</param>
        /// <returns></returns>
        public static bool PingHost(string IPAddress, int timeout)
        {
            try
            {
                Ping pingSender = new Ping();
                PingOptions options = new PingOptions();

                // Use the default Ttl value which is 128,
                // but change the fragmentation behavior.
                options.DontFragment = true;

                // Create a buffer of 32 bytes of data to be transmitted.
                string data = "Hoang Quoc Dung";
                byte[] buffer = Encoding.ASCII.GetBytes(data);

                PingReply reply = pingSender.Send(IPAddress, timeout, buffer, options);
                if (reply.Status == IPStatus.Success)
                    return true;
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }

        public static DataTable GetUserUnitServerTable(string strConnection, string strUserLogin)
        {
            //Return a DataTable by a select Statement
            DataTable dtTemp;
            dtTemp = null;
            MySqlConnection conn;
            MySqlDataAdapter adap;

            //username,servername,dbname,ipaddress,userid,passwordid, tagprefix
            string strSQLQuery = "SELECT userserver.username " + Environment.NewLine
                                + "      , serverlist.servername " + Environment.NewLine
                                + "      , serverlist.dbname " + Environment.NewLine
                                + "      , serverlist.ipaddress " + Environment.NewLine
                                + "      , serverlist.dbuserid AS userid " + Environment.NewLine
                                + "      , serverlist.dbpassword AS passwordid " + Environment.NewLine
                                + "      , serverlist.tagprefix " + Environment.NewLine
                                + "FROM sys_user_server userserver INNER JOIN sys_serverlist serverlist ON userserver.ServerName = serverlist.ServerName" + Environment.NewLine
                                + "WHERE serverlist.IsActive = 1 AND userserver.UserName = '" + strUserLogin + "' ";

            try
            {
                conn = new MySqlConnection(strConnection);
                conn.Open();

                adap = new MySqlDataAdapter(strSQLQuery, conn);
                dtTemp = new DataTable();
                adap.Fill(dtTemp);

                //Decode passwordid field
                if (dtTemp != null)
                {
                    foreach (DataRow RowItem in dtTemp.Rows)
                    {
                        if (RowItem["passwordid"] != DBNull.Value)
                        {
                            RowItem.BeginEdit();

                            RowItem["passwordid"] = NSecurity.DecryptString(RowItem["passwordid"].ToString());

                            RowItem.EndEdit();
                        }
                    }
                    dtTemp.AcceptChanges();
                }

                dtTemp.TableName = "USER_SERVER";

                conn.Close();
            }
            catch (Exception)
            {
                return null;
            }

            return dtTemp;
        }

        #region Server Level


        public static bool SetServerInfor(string ServerLevel, Int64 SiteServerIndex, Int64 HeadquarterServerIndex)
        {
            NDatabase objDAL = new NDatabase();

            try
            {
                return objDAL.fcnExecNonQuery("UPDATE sys_server_info " + 
                                              "SET Server_Level='" + ServerLevel + "', " +
                                                  "Site_ServerID=" + SiteServerIndex + ", " +
                                                  "HeadQuarter_ServerID=" + HeadquarterServerIndex);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static bool InsertServerInfor(string ServerLevel, Int64 SiteServerIndex, Int64 HeadquarterServerIndex)
        {
            NDatabase objDAL = new NDatabase();

            try
            {
                return objDAL.fcnExecNonQuery("INSERT INTO sys_server_info(Server_Level, Site_ServerID,HeadQuarter_ServerID) " + 
                                              "VALUES('" + ServerLevel + "', " +
                                                         + SiteServerIndex + ", " +
                                                         + HeadquarterServerIndex +")");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        public static DataTable GetServerListActivated(string strConnection)
        {
            //Return a DataTable of Table sys_serverlist
            DataTable dtTemp;
            dtTemp = null;
            MySqlConnection conn;
            MySqlDataAdapter adap;

            //username,servername,dbname,ipaddress,userid,passwordid, tagprefix
            string strSQLQuery = "SELECT sys_server.servername " + Environment.NewLine
                                + "      , sys_server.description " + Environment.NewLine
                                + "      , sys_server.ipaddress " + Environment.NewLine
                                + "      , sys_server.dbname " + Environment.NewLine
                                + "      , sys_server.dbuserid " + Environment.NewLine
                                + "      , sys_server.dbpassword " + Environment.NewLine
                                + "      , sys_server.tagprefix " + Environment.NewLine
                                + "FROM sys_serverlist sys_server " + Environment.NewLine
                                + "WHERE sys_server.IsActive = 1";

            try
            {
                conn = new MySqlConnection(strConnection);
                conn.Open();

                adap = new MySqlDataAdapter(strSQLQuery, conn);
                dtTemp = new DataTable();
                adap.Fill(dtTemp);

                if (dtTemp != null)
                {
                    dtTemp.TableName = "sys_serverlist";
                }

                conn.Close();
            }
            catch (Exception)
            {
                return null;
            }

            return dtTemp;
        }

    }
}
